﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using DTO;
namespace DAO
{
    public class VeMayBayDAO
    {
          DataTable dt;
        List<string> dsThamSo;
        ArrayList dsGiaTri;
        public VeMayBayDAO()
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            
        }
        public DataTable layThongTinTinhPhi(string maVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_TinhPhiHuyVe", dsThamSo, dsGiaTri);
            return dt;
            
        }
        public void HuyVe(string maVe,string maNhanVien, string soThe,float giaDoi,string ngayGhiPhieu)
        {
            string sql = "update VeMayBay set tinhTrang='True' where ID_veBay = '" + maVe + "'";
            DataProvider.ExecuteNoneQuery(sql);
            //dsThamSo.Add("@soThe");
            //dsGiaTri.Add(soThe);
            //dt = DataProvider.ExecuteStoreProc("sp_layMaKhachHang", dsThamSo, dsGiaTri);
            //string maKhach = "";
            //if(dt.Rows.Count>0)
            //{
            //    maKhach = dt.Rows[0]["ID_khach"].ToString();
            //}
            string sql2 = "insert into PhieuGhiNhan values('" + maNhanVien + "'  ,'" + maVe + "',N'Hủy'," + giaDoi + ",'" + ngayGhiPhieu + "')";
            DataProvider.ExecuteNoneQuery(sql2);
        }
        public bool kiemTraHuyVe(string maVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_KiemTraHuyVe", dsThamSo, dsGiaTri);
            if (dt.Rows.Count > 0)
            {
                if (bool.Parse(dt.Rows[0]["tinhTrang"].ToString()) == true)
                    return false;
                else
                    return true;
            }
            return false;
        }
        public DataTable layThongTinVeBay(string maVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_LayThongTinVe", dsThamSo, dsGiaTri);
            return dt;

        }
        public DataTable LaySoChoNgoi(string maVe, int loaiVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dsThamSo.Add("@loaiVe");
            dsGiaTri.Add(loaiVe);
            dt = DataProvider.ExecuteStoreProc("sp_kiemTraSoCho", dsThamSo, dsGiaTri);
            return dt;

        }

        public DataTable tinhPhiNangHang(string maVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_tinhPhiNangHang", dsThamSo, dsGiaTri);
            return dt;
        }
        public int NangHangVe(string maVe, int loaiVeCu, int loaiVeMoi,string maNhanVien,string maChuyenBay,float phi)
        {
            int n1 = 0;
            int n2 = 0;
            int n3 = 0;
            int n4 = 0;
            string sql1 = "update VeMayBay set ID_loaiVe =" + loaiVeMoi + " where ID_veBay= '" + maVe + "'";
            n1 = DataProvider.ExecuteNoneQuery(sql1);
            string sql2 = "update BangGiaVe set soChoHienTai += 1   where ID_loaiVe =" + loaiVeCu + " and ID_chuyenBay = '" + maChuyenBay+ "'";
            n2 = DataProvider.ExecuteNoneQuery(sql2);
            string sql3 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + loaiVeMoi + " and ID_chuyenBay = '" + maChuyenBay + "'";
            n3 = DataProvider.ExecuteNoneQuery(sql3);
            string sql4 = "insert into PhieuGhiNhan(ID_nhanVien,ID_maVe,mucDich,phi,ngayGhiPhieu) values('" + maNhanVien + "','" + maVe + "',N'Nâng Hạng'," + phi + ",'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "')";
            n4 = DataProvider.ExecuteNoneQuery(sql4);
            return n1 + n2 + n3 + n4;
        }
        public DataTable tinhPhiChuyenDoiLichTrinh(string maVe)
        {
            dsThamSo.Add("@maVe");
            dsGiaTri.Add(maVe);
            dt = DataProvider.ExecuteStoreProc("sp_tinhPhiChuyenDoiLichTrinh", dsThamSo, dsGiaTri);
            return dt;
        }
        public void chuyenDoiLichTrinh(string maVecu,int hangVeCu,string ChuyenBayCu,string maKhachHang,string maNhanVien,string maVeDi,string maChuyenBayDi,int hangVeDi,string maVeVe,string maChuyenVe,int hangVeVe,float phiDoiLichTrinh)
        {
          
            if (maChuyenVe == "")
            {
                string sql1 = "update VeMayBay set tinhTrang= 'True' where ID_veBay ='" + maVecu + "'";
                DataProvider.ExecuteNoneQuery(sql1);
                string sql2 = "insert into PhieuGhiNhan(ID_nhanVien,ID_maVe,mucDich,phi,ngayGhiPhieu) values('" + maNhanVien + "','" + maVecu + "',N'Đổi'," + phiDoiLichTrinh + ",'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "')";
                DataProvider.ExecuteNoneQuery(sql2);
                string sql3 = "update BangGiaVe set soChoHienTai += 1   where ID_loaiVe =" + hangVeCu + " and ID_chuyenBay = '" + ChuyenBayCu + "'";
                DataProvider.ExecuteNoneQuery(sql3);
                string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + maKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                DataProvider.ExecuteNoneQuery(sql4);
                string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                DataProvider.ExecuteNoneQuery(sql5);
            }
            else
                if (maChuyenBayDi == "")
                {
                    string sql1 = "update VeMayBay set tinhTrang= 'True' where ID_veBay ='" + maVecu + "'";
                    DataProvider.ExecuteNoneQuery(sql1);
                    string sql2 = "insert into PhieuGhiNhan(ID_khachHang,ID_maVe,mucDich,phi,ngayGhiPhieu) values('" + maKhachHang + "','" + maVecu + "',N'Đổi'," + phiDoiLichTrinh + ",'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "')";
                    DataProvider.ExecuteNoneQuery(sql2);
                    string sql3 = "update BangGiaVe set soChoHienTai += 1   where ID_loaiVe =" + hangVeCu + " and ID_chuyenBay = '" + ChuyenBayCu + "'";
                    DataProvider.ExecuteNoneQuery(sql3);
                    string sql4 = "insert into VeMayBay values('" + maVeVe + "','" + maKhachHang + "','" + hangVeVe + "','" + maChuyenVe + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                    DataProvider.ExecuteNoneQuery(sql4);
                    string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeVe + " and ID_chuyenBay = '" + maChuyenVe + "'";
                    DataProvider.ExecuteNoneQuery(sql5);
                }
                else
                    if (maChuyenBayDi != "" && maChuyenVe != "")
                    {
                        string sql1 = "update VeMayBay set tinhTrang= 'True' where ID_veBay ='" + maVecu + "'";
                        DataProvider.ExecuteNoneQuery(sql1);
                        string sql2 = "insert into PhieuGhiNhan(ID_khachHang,ID_maVe,mucDich,phi,ngayGhiPhieu) values('" + maKhachHang + "','" + maVecu + "',N'Đổi'," + phiDoiLichTrinh + ",'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "')";
                        DataProvider.ExecuteNoneQuery(sql2);
                        string sql3 = "update BangGiaVe set soChoHienTai += 1   where ID_loaiVe =" + hangVeCu + " and ID_chuyenBay = '" + ChuyenBayCu + "'";
                        DataProvider.ExecuteNoneQuery(sql3);
                        string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + maKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                        DataProvider.ExecuteNoneQuery(sql4);
                        string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                        DataProvider.ExecuteNoneQuery(sql5);
                        string sql6 = "insert into VeMayBay values('" + maVeVe + "','" + maKhachHang + "','" + hangVeVe + "','" + maChuyenVe + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                        DataProvider.ExecuteNoneQuery(sql6);
                        string sql7 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeVe + " and ID_chuyenBay = '" + maChuyenVe + "'";
                        DataProvider.ExecuteNoneQuery(sql7);

                    }
        }
        public DataTable layDanhSachVe(string maChuyenBay)
        {
            dsThamSo.Add("@maChuyenBay");
            dsGiaTri.Add(maChuyenBay);
            dt = DataProvider.ExecuteStoreProc("sp_LayDanhSachVe", dsThamSo, dsGiaTri);
            return dt;
        }
        public int capNhatVeMayBay(string maVe,string viTri)
        {
            int n1 = 0;
           
            string sql = "update VeMayBay set viTri ='" + viTri + "' where ID_veBay= '" + maVe + "'";
            n1 = DataProvider.ExecuteNoneQuery(sql);
            return n1;
        }
        public void datVe(KhachHangDTO kh, string maVeDi, string maChuyenBayDi, int hangVeDi, string maVeVe, string maChuyenVe, int hangVeVe, int doiTuong)
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@soThe");
            dsGiaTri.Add(kh.SoThe);
            dt = DataProvider.ExecuteStoreProc("sp_layMaKhachHang", dsThamSo, dsGiaTri);
           string maKhachHang = "";
         
           if (dt.Rows.Count > 0)
           {
               maKhachHang = dt.Rows[0]["ID_khach"].ToString();
           }
           else
           {
              kh.MaKhachHang = "KH" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString();
           }

            if (maChuyenVe == "")
            {
                if (maKhachHang == "")
                {
                    string strsql = "insert into Khach values('" + kh.MaKhachHang + "',";
                    if (kh.MaHoiVien == "")
                        strsql += "null,";
                    else
                        strsql += "'" + kh.MaHoiVien + "',";
                    strsql += "'" + kh.HoTen + "','" + kh.GioiTinh + "','" + kh.NgaySinh + "','" + kh.Email + "','" + kh.SoDienThoaiDiDong + "','" + kh.SoDienThoaiBan + "' ";
                    strsql += "  ,'" + kh.LoaiGiayTo + "','" + kh.SoThe + "','" + kh.NuocPhatHanh + "','" + kh.QuocTich + "','" + kh.NgayHetHan + "'," + doiTuong + ",0)";
                    DataProvider.ExecuteNoneQuery(strsql);
                    string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + kh.MaKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                    DataProvider.ExecuteNoneQuery(sql4);
                    string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                    DataProvider.ExecuteNoneQuery(sql5);
                }
                else
                {
                    string strsql = "update Khach set hoTen = N'" + kh.HoTen + "' ,";
                    strsql += "gioiTinh = '" + kh.GioiTinh + "' ,";
                    strsql += "ngaySinh = '" + kh.NgaySinh + "' ,";
                    if (kh.MaHoiVien == "")
                        strsql += "ID_hoiVien = NULL ,";
                    else
                        strsql += "ID_hoiVien = '" + kh.MaHoiVien + "' ,";
                    strsql += "loaiGiayTo = '" + kh.LoaiGiayTo + "' ,";
                    strsql += "soThe = '" + kh.SoThe + "' ,";
                    strsql += "nuocPhatHanh = '" + kh.NuocPhatHanh + "' ,";
                    strsql += "ngayHetHan = '" + kh.NgayHetHan + "' ,";
                    strsql += "quocTich = '" + kh.QuocTich + "',";
                    strsql += "email = '" + kh.Email + "'";
                    strsql += " where ID_khach = '" + maKhachHang + "'";
                    DataProvider.ExecuteNoneQuery(strsql);
                    string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + maKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                    DataProvider.ExecuteNoneQuery(sql4);
                    string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                    DataProvider.ExecuteNoneQuery(sql5);
                }
           
            }
            
                else
                    if (maChuyenBayDi != "" && maChuyenVe != "")
                    {
                        if (maKhachHang == "")
                        {
                            string strsql = "insert into Khach values('" + kh.MaKhachHang + "',";
                            if (kh.MaHoiVien == "")
                                strsql += "null,";
                            else
                                strsql += "'" + kh.MaHoiVien + "',";
                            strsql += "'" + kh.HoTen + "','" + kh.GioiTinh + "','" + kh.NgaySinh + "','" + kh.Email + "','" + kh.SoDienThoaiDiDong + "','" + kh.SoDienThoaiBan + "' ";
                            strsql += "  ,'" + kh.LoaiGiayTo + "','" + kh.SoThe + "','" + kh.NuocPhatHanh + "','" + kh.QuocTich + "','" + kh.NgayHetHan + "'," + doiTuong + ",0)";
                            DataProvider.ExecuteNoneQuery(strsql);
                            string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + kh.MaKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                            DataProvider.ExecuteNoneQuery(sql4);
                            string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                            DataProvider.ExecuteNoneQuery(sql5);
                            string sql6 = "insert into VeMayBay values('" + maVeVe + "','" + kh.MaKhachHang + "','" + hangVeVe + "','" + maChuyenVe + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                            DataProvider.ExecuteNoneQuery(sql6);
                            string sql7 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeVe + " and ID_chuyenBay = '" + maChuyenVe + "'";
                            DataProvider.ExecuteNoneQuery(sql7);
                        }
                        else
                        {
                            string strsql = "update Khach set hoTen = N'" + kh.HoTen + "' ,";
                            strsql += "gioiTinh = '" + kh.GioiTinh + "' ,";
                            strsql += "ngaySinh = '" + kh.NgaySinh + "' ,";
                            if (kh.MaHoiVien == "")
                                strsql += "ID_hoiVien = NULL ,";
                            else
                                strsql += "ID_hoiVien = '" + kh.MaHoiVien + "' ,";
                            strsql += "loaiGiayTo = '" + kh.LoaiGiayTo + "' ,";
                            strsql += "soThe = '" + kh.SoThe + "' ,";
                            strsql += "nuocPhatHanh = '" + kh.NuocPhatHanh + "' ,";
                            strsql += "ngayHetHan = '" + kh.NgayHetHan + "' ,";
                            strsql += "quocTich = '" + kh.QuocTich + "',";
                            strsql += "email = '" + kh.Email + "'";
                            strsql += " where ID_khach = '" + maKhachHang + "'";
                            DataProvider.ExecuteNoneQuery(strsql);
                            string sql4 = "insert into VeMayBay values('" + maVeDi + "','" + maKhachHang + "','" + hangVeDi + "','" + maChuyenBayDi + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                            DataProvider.ExecuteNoneQuery(sql4);
                            string sql5 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeDi + " and ID_chuyenBay = '" + maChuyenBayDi + "'";
                            DataProvider.ExecuteNoneQuery(sql5);
                            string sql6 = "insert into VeMayBay values('" + maVeVe + "','" + maKhachHang + "','" + hangVeVe + "','" + maChuyenVe + "',NULL,'" + string.Format("{0:yyyy-MM-dd}", DateTime.Now) + "','True','False','False')";
                            DataProvider.ExecuteNoneQuery(sql6);
                            string sql7 = "update BangGiaVe set soChoHienTai -= 1   where ID_loaiVe =" + hangVeVe + " and ID_chuyenBay = '" + maChuyenVe + "'";
                            DataProvider.ExecuteNoneQuery(sql7);
                        }
                      

                    }
        }
        public DataTable layThongTinDatVeB3(string maChuyenMay, int hangVe)
        {
            dt = new DataTable();
            dsThamSo = new List<string>();
            dsGiaTri = new ArrayList();
            dsThamSo.Add("@maChuyenBay");
            dsThamSo.Add("@loaiVe");
            dsGiaTri.Add(maChuyenMay);
            dsGiaTri.Add(hangVe);
            dt = DataProvider.ExecuteStoreProc("sp_layThongTinDatVe_B3", dsThamSo, dsGiaTri);
            return dt;
        }
    }
}
